﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TR.Domain.Entity;
using TR.Domain.Entity.BsItemType;
using TR.Domain.Repository.BS_Item_TypeRepository;
using TR.Domain.Repository.BSType;

namespace TR.EFCore.Repository.BS
{
    public class bsItemTypeRepository : IBsItemTypeRepository
    {
        private readonly MyContext context;

        public bsItemTypeRepository(MyContext context)
        {
            this.context = context;
        }
        private int fyzts;

        public async Task<List<bs_Item_Type>> bs_ItemType_FenYeAsync(int pageSize, int currentPage, string kssj,string jssj,bs_Item_Type bit)
        {
            //声明一个变量
            var query = context.bs_Item_Type.AsQueryable();
            query = query.Where(p => p.is_del == 0);
            if (!string.IsNullOrEmpty(kssj) && !string.IsNullOrEmpty(jssj))
            {
                query = query.Where(p => string.Compare(p.create_time, kssj) >= 0
                               && string.Compare(p.create_time, jssj) <= 0);
            }

            ////条件  StringComparison.OrdinalIgnoreCase 模糊查询
            if (bit != null)
            {
                if (!string.IsNullOrEmpty(bit.item_type_name))
                {
                    query = query.Where(p => EF.Functions.Like(p.item_type_name, "%" + bit.item_type_name + "%"));
                }
                
                if (!string.IsNullOrEmpty(bit.create_time))
                {
                    query = query.Where(p => p.create_time == bit.create_time);
                }
               

            }
            var result = await query
                 .OrderByDescending(p => p.item_type_code)
                  .Skip(pageSize * (currentPage - 1))
                  .Take(pageSize)
                  .ToListAsync();

            fyzts = await query.CountAsync(); // 获取符合查询条件的记录数

            return result; // 返回查询结果
        }

        public async Task<int> count()
        {
            return fyzts;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="Itemtype"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<bool> ItemTypeAdd(bs_Item_Type Itemtype)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                // 获取当前时间
                DateTime currentTime = DateTime.Today;

                string sql = $"INSERT INTO [dbo].[bs_Item_Type]([item_type_code],[item_type_name],[father_code],[sort_code],[inbill_state],[remark],[create_by],[create_time],[update_by],[update_time],[is_del]) " +
                             $"VALUES ('{Itemtype.item_type_code}','{Itemtype.item_type_name}','{Itemtype.father_code}','{Itemtype.sort_code}','{Itemtype.inbill_state}','{Itemtype.remark}','admin','{currentTime}','{Itemtype.update_by}','{Itemtype.update_time}',0)";

                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="item_type_code"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<bool> ItemtypeUDelect(string item_type_code)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"delete from bs_Item_Type where [item_type_code] = '{item_type_code}'";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        public async Task<bool> ItemTypeUpdate(UpdateItemTypeDto usd)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"Update [dbo].[bs_Item_Type] Set [item_type_code] ='{usd.item_type_code}' ,[item_type_name]='{usd.item_type_name}',[father_code]='{usd.father_code}',[sort_code]='{usd.sort_code}',[inbill_state]='{usd.inbill_state}',[remark]='{usd.remark}',[create_by]='{usd.create_by}',[create_time]='{usd.create_time}', [update_by]='{usd.update_by}',[update_time]='{usd.update_time}',[is_del]='{usd.is_del}' where [id] ='{usd.id}' ";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        public async Task<bs_Item_Type> ItemTypeSelectid(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_Item_Type] where [id] = @id";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_Item_Type>(sql, new { id = id });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
    }
}
